package com.bw.gmallpublisher.mapper;

import com.bw.gmallpublisher.bean.ShopAllBean;
import com.bw.gmallpublisher.bean.ShopByHourBean;
import com.bw.gmallpublisher.bean.TrafficVisitorTypeStats;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ShopMapper {
    /*
    --  分时段趋势统计
--  分时段趋势统计
select t1.*,t2.*  from(SELECT 1 AS hour
               UNION ALL SELECT 2
               UNION ALL SELECT 3
               UNION ALL SELECT 4
               UNION ALL SELECT 5
               UNION ALL SELECT 6
               UNION ALL SELECT 7
               UNION ALL SELECT 8
               UNION ALL SELECT 9
               UNION ALL SELECT 10
               UNION ALL SELECT 11
               UNION ALL SELECT 12
               UNION ALL SELECT 13
               UNION ALL SELECT 14
               UNION ALL SELECT 15
               UNION ALL SELECT 16
               UNION ALL SELECT 17
               UNION ALL SELECT 18
               UNION ALL SELECT 19
               UNION ALL SELECT 20
               UNION ALL SELECT 21
               UNION ALL SELECT 22
               UNION ALL SELECT 23
               UNION ALL SELECT 24)t1
left  join (
select
    '20241031'  dt,
    toHour(stt) hr,
    shop_id,
    device_type,
    sum(uniqueUserCount) uv_ct,
    sum(pv) pv_ct
from dws_shop
where toYYYYMMDD(stt) =20241031 and  shop_id=5  and  device_type=0
      group by toHour(stt) ,shop_id,device_type
    ) t2  on  t1.hour=t2.hr;

-- 累计统计
select
    '20241031' dt,
      shop_id,
       device_type,
    sum(uniqueUserCount) uv_ct,
    sum(pv) pv_ct
from dws_shop
where toYYYYMMDD(stt) =20241031   and   shop_id=5  and  device_type=0
group by shop_id,device_type
    * */
    @Select("select t1.hour  hr,t2.shop_id,t2.device_type,t2.uv_ct,t2.pv_ct  from(SELECT 1 AS hour\n" +
            "               UNION ALL SELECT 2\n" +
            "               UNION ALL SELECT 3\n" +
            "               UNION ALL SELECT 4\n" +
            "               UNION ALL SELECT 5\n" +
            "               UNION ALL SELECT 6\n" +
            "               UNION ALL SELECT 7\n" +
            "               UNION ALL SELECT 8\n" +
            "               UNION ALL SELECT 9\n" +
            "               UNION ALL SELECT 10\n" +
            "               UNION ALL SELECT 11\n" +
            "               UNION ALL SELECT 12\n" +
            "               UNION ALL SELECT 13\n" +
            "               UNION ALL SELECT 14\n" +
            "               UNION ALL SELECT 15\n" +
            "               UNION ALL SELECT 16\n" +
            "               UNION ALL SELECT 17\n" +
            "               UNION ALL SELECT 18\n" +
            "               UNION ALL SELECT 19\n" +
            "               UNION ALL SELECT 20\n" +
            "               UNION ALL SELECT 21\n" +
            "               UNION ALL SELECT 22\n" +
            "               UNION ALL SELECT 23\n" +
            "               UNION ALL SELECT 24)t1\n" +
            "left  join (\n" +
            "select\n" +
            "    '20241031'  dt,\n" +
            "    toHour(stt) hr,\n" +
            "    shop_id,\n" +
            "    device_type,\n" +
            "    sum(uniqueUserCount) uv_ct,\n" +
            "    sum(pv) pv_ct\n" +
            "from dws_shop\n" +
            "where toYYYYMMDD(stt) =#{date} and  shop_id=#{shop_id}  and  device_type=#{device_type}\n" +
            "      group by toHour(stt) ,shop_id,device_type\n" +
            "    ) t2  on  t1.hour=t2.hr")
    List<ShopByHourBean> ShopMapByHr(@Param("date") Integer date,
                                     @Param("shop_id") Integer shop_id,
                                     @Param("device_type") Integer device_type);
    @Select("select\n" +
            "    '20241031' dt,\n" +
            "      shop_id,\n" +
            "       device_type,\n" +
            "    sum(uniqueUserCount) uv_ct,\n" +
            "    sum(pv) pv_ct\n" +
            "from dws_shop\n" +
            "where toYYYYMMDD(stt) =#{date} and  shop_id=#{shop_id}  and  device_type=#{device_type}\n" +
            "group by shop_id,device_type")
    ShopAllBean ShopMapAll(@Param("date") Integer date,
                                 @Param("shop_id") Integer shop_id,
                                 @Param("device_type") Integer device_type);
}
